﻿-- ++++++++++ Start of Procedure Creation ++++++++++
Use Ji
GO

CREATE PROCEDURE CreateTable
AS
	CREATE TABLE Table1 
		(Table1ID int not null, 
		TweetID decimal not null, 
		DateTimeStamp datetime not null,
		Latitude float not null, 
		Longitude float not null, 
		TweetContent text null, 
		PoliceOrNot bit not null, 
		primary key(TweetID, DateTimeStamp))
GO


CREATE PROCEDURE InsertTable_Initial
As
	INSERT Table1
		(Table1ID, TweetID, DateTimeStamp, Latitude, Longitude, TweetContent, PoliceOrNot)
	VALUES
		('0', '5', GETDATE(), '1', '1', 'Hello World', '1'),
		('0', '10', GETDATE(), '1', '1', 'A Robery!', '0'),
		('0', '12', GETDATE(), '1', '1', 'Attention all unit, suspect wearing red hat...', '1'),
		('0', '23', GETDATE(), '1', '1', 'Polices are all heading to downtown!', '0')
GO

CREATE PROCEDURE InsertTable
	@id decimal,
	@lat float,
	@lon float,
	@time datetime,
	@info text,
	@isPDTwt bit
As
	INSERT Table1
		(Table1ID, TweetID, DateTimeStamp, Latitude, Longitude, TweetContent, PoliceOrNot)
	VALUES
		('0', @id, @time, @lat, @lon, @info, @isPDTwt)
GO

CREATE PROCEDURE SelectAll
AS
	SELECT * FROM Table1 ORDER BY DateTimeStamp
GO

CREATE FUNCTION SelectByCondition (@DateTimeStampLower datetime, @DateTimeStampUpper datetime)
RETURNS @ResultTable table (TweetID decimal not null,
							DateTimeStamp datetime,
							Latitude float not null,
							Longitude float not null,
							TweetContent text null, 
							PoliceOrNot bit not null, 
							primary key(TweetID, DateTimeStamp))
AS
BEGIN
	INSERT INTO @ResultTable 
		SELECT TweetID, Latitude, Longitude FROM Table1 WHERE DatetimeStamp >= @DatetimeStampLower AND DateTimeStamp <= @DateTimeStampUpper
	RETURN
END
GO

CREATE PROCEDURE SelectByCondition
	@DateTimeStampLower datetime,
	@DateTimeStampUpper datetime
AS
	SELECT * FROM Table1 WHERE DatetimeStamp >= @DatetimeStampLower AND DateTimeStamp <= @DateTimeStampUpper
GO
	
-- ++++++++++ End of Procedure Creation ++++++++++

EXEC CreateTable;
GO

EXEC InsertTable_Initial;
GO

DECLARE @id decimal, @time datetime, @lat float, @lon float, @info nvarchar(4000), @isPDTwt bit;
SET @id = '25';
SET @time = GETDATE();
SET @lat = '1';
SET @lon = '1';
SET @info = 'This guy thinks he can fly!';
SET @isPDTwt = '1';
--SELECT @id = '25', @time = GETDATE(), @lat = '1', @long = '1', @isPDTwt = '1';
--EXEC InsertTable '25', '1', '1', '2012-11-09 12:19:25.163', 'This guy thinks he can fly!', '1';
EXEC InsertTable @id, @lat, @lon, @time, @info, @isPDTwt;
GO

EXEC SelectByCondition @DateTimeLower, @DateTimeUpper;
GO

EXEC SelectAll;
GO

DROP TABLE Table1;
GO

DROP PROCEDURE CreateTable;
GO

DROP PROCEDURE InsertTable_Initial;
GO

DROP PROCEDURE InsertTable;
GO

DROP PROCEDURE SelectAll;
GO

DROP PROCEDURE SelectByCondition;
GO

SELECT * FROM SelectByCondition('2012-11-09 12:38:49.237', '2012-11-09 12:40:19.703');
GO

DROP FUNCTION SelectByCondition;
GO

DECLARE @local int;
SET @local='37';
PRINT @local;
GO